#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	#echo 必须给我传一个日期
	#exit
	do_date= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$do_date
dws_play_course_video_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
with pv as (
    select
        count(distinct user_id) user_count,   --观看人数
        sum(play_sec) sum_play_sec,          --观看时长
        video_id ,
        count(distinct sid) sid_count,  --视频播放次数
        play_time ,
        course_id ,
        chapter_id
    from dwd_paly_video_inc
    where dt<='$do_date'
    group by course_id, video_id, play_time, chapter_id

),cin as (
    select
        id,
           course_name
    from dim_course_info_full
    where dt='$do_date'
),ci as (
    select
        id,
           chapter_name
    from dim_chapter_info_full
    where dt='$do_date'
),vi as (
    select
        id,
           video_name
    from ods_video_info_full
    where dt='$do_date'
)
insert overwrite table dws_play_course_video_1d partition (dt='$do_date')
select
    user_count,
       sum_play_sec,
       video_id ,
       video_name,
       sid_count,
       play_time,
       course_id ,
       course_name,
       chapter_id,
       chapter_name
from pv left join cin
    on pv.course_id=cin.id
left join ci
    on pv.chapter_id=ci.id
left join vi
    on pv.video_id=vi.id;
"
dws_play_course_course_1d="
    with pc as (
    select
        id     ,
        course_id   ,
        count(chapter_id)  count_chapter,          --完播章节数
        count(distinct user_id)  count_user,           --完播章节人数
        count( user_id)  counted_user,  --完播章节人次
        create_time ,
        position_sec,
        during_sec  ,
        play_sec    ,
        sid         ,
        video_id    ,
        video_name  ,
        video_status,
        version_id
    from dwd_paly_course_inc
    where dt<='$do_date'
        and play_sec>=during_sec * 0.9
        and position_sec>=during_sec * 0.9
    group by id, course_id, create_time, position_sec, during_sec, play_sec, sid, video_id, video_name, video_status, version_id
),cin as (
    select
        id,
           course_name,
           chapter_num
    from dim_course_info_full
    where dt='$do_date'
)
insert overwrite table dws_play_course_course_1d partition(dt)
select
    pc.id     ,
    course_id   ,
    course_name,
    chapter_num,
    count_chapter  ,
    count_user,
    counted_user ,
    create_time ,
    position_sec,
    during_sec  ,
    play_sec    ,
    sid         ,
    video_id    ,
    video_name  ,
    video_status,
    version_id,
    create_time
from pc left join cin
    on pc.course_id=cin.id
where chapter_num=count_chapter;
"
dws_traffic_session_page_view_1d="
insert overwrite table dws_traffic_session_page_view_1d partition(dt=''$do_date'')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time),
       -- 页面次数
    count(*)
from dwd_traffic_page_view_inc
where dt=''$do_date''
group by session_id,
         mid_id,brand,model,operate_system,version_code,channel;
"
dws_trade_user_cart_add_1d="
insert overwrite table dws_trade_user_cart_add_1d partition(dt=''$do_date'')
select
    user_id,
    count(*),
    sum(course_id)
from dwd_trade_cart_add_inc
where dt=''$do_date''
group by user_id;
"
dws_trade_user_payment_1d="
insert overwrite table dws_trade_user_payment_1d partition(dt='$do_date')
select
       user_id,
       count(distinct order_id) pay_count_1d,
       sum(course_id) pay_sku_num_1d,
       sum(final_amount) pay_money_1d
from dwd_trade_pay_detail_suc_inc
where dt='$do_date'
group by user_id;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,page_id
,brand,model,operate_system;
"
dws_class_course_order_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
with class as (
    select
           id,
           course_name,
           subject_id,
           subject_name,
           category_id,
           category_name,
           dt
    from dim_course_info_full
    where dt='$do_date'
), od as (
    select
           dt,
           course_id,
           count(order_id) order_count_1d,
           count(distinct user_id) number_orders_1d,
           sum(final_amount) order_amount_1d
    from dwd_trade_order_detail_inc
    where dt='$do_date'
    group by dt, course_id
)
insert overwrite table dws_class_course_order_1d partition (dt)
select
       id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       order_count_1d,
       number_orders_1d,
       order_amount_1d,
       od.dt
from class
left join od on id=od.course_id;
"
dws_class_course_review_1d="
insert overwrite table dws_class_course_review_1d partition (dt)
select course_id,
       course_name,
       avg(review_stars) review_avg,
       count(user_id) review_user_count,
       count(`if`(review_stars>4,review_stars,null))/count(user_id),
       dt
from dwd_interaction_review_info_inc
where dt='$do_date' and course_id is not null
group by course_id,
         course_name,
         dt;
"
dws_class_course_audition_1d="
with log as (
    select
           common.uid
    from ods_log_inc
    where appvideo.video_id is not null and common.is_new=1 and dt='$do_date'
),pay as (
    select
           course_id,
           course_name,
           user_id,
           dt
    from dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
),num as (
    select count(1) audition_count_1d
    from ods_log_inc
    where appvideo.video_id is not null and common.is_new=1 and dt='$do_date' and common.uid is not null
),sub as (
    select id,
           category_id,
           category_name,
           subject_name,
           subject_id
    from dim_course_info_full
)
insert overwrite table dws_class_course_audition_1d partition (dt)
select course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       audition_count_1d,
       count(user_id)/audition_count_1d,
       dt
from log
left join pay on user_id=uid
join num
left join sub on id=course_id
group by course_id, course_name,subject_id,subject_name,category_id,category_name;
"

case $2 in
"dws_play_course_video_1d")
	hive --database gmall -e "$dws_play_course_video_1d"
	;;
"dws_play_course_course_1d")
	hive --database gmall -e "$dws_play_course_course_1d"
	;;
"dws_traffic_session_page_view_1d")
	hive --database gmall -e "$dws_traffic_session_page_view_1d"
	;;
"dws_trade_user_cart_add_1d")
	hive --database gmall -e "$dws_trade_user_cart_add_1d"
	;;
"dws_trade_user_payment_1d")
	hive --database gmall -e "$dws_trade_user_payment_1d"
	;;
"dws_traffic_page_visitor_page_view_1d")
	hive --database gmall -e "$dws_traffic_page_visitor_page_view_1d"
	;;
"dws_class_course_order_1d")
	hive --database gmall -e "$dws_class_course_order_1d"
	;;
"dws_class_course_review_1d")
	hive --database gmall -e "$dws_class_course_review_1d"
	;;
"dws_class_course_audition_1d")
	hive --database gmall -e "$dws_class_course_audition_1d"
	;;
"all")
	hive --database gmall -e "$dws_play_course_video_1d$dws_play_course_course_1d$dws_trade_user_cart_add_1d$dws_trade_user_payment_1d$dws_traffic_page_visitor_page_view_1d$dws_class_course_order_1d$dws_class_course_review_1d$dws_class_course_audition_1d"
	;;
esac